from openpyxl import load_workbook
import time

t0 = time.time()
head = open("head.txt",mode='r')    #临时Kml头文件
point = open("point.kml",mode='w',encoding='utf-8-sig')  #输出KML文件
tmp = head.readlines()
point.writelines(tmp)
head.close()

WorkBook   = load_workbook("point.xlsx")
Sheet1 = WorkBook['Sheet1']
maxRows = Sheet1.max_row
maxColumn = Sheet1.max_column

tmp  = "\t\t<Folder>\n"
tmp = tmp + "\t\t\t<name>站点</name>\n"
point.writelines(tmp)

for i in range(2,maxRows+1):
    name = Sheet1.cell(row=i,column=1).value
    lon  = Sheet1.cell(row=i,column=2).value
    lat  = Sheet1.cell(row=i,column=3).value

    print("第"+str(i-1)+"行："+name)
    tmp = "\t\t\t<Placemark>\n"
    tmp = tmp +"\t\t\t\t<name>"+name+"</name>\n"
    tmp = tmp +"\t\t\t\t<description>"+"<![CDATA[<table border=1 width=650>\n"
    col = 1
    for group in range(0,round(maxColumn/4)+1):
        #print(group,col,round(maxColumn/4))
        h1 = str(Sheet1.cell(row=1,column=col).value)
        if h1 == "None":
            h1 = " "
        h2 = str(Sheet1.cell(row=1,column=col+1).value)
        if h2 == "None":
            h2 = " "
        h3 = str(Sheet1.cell(row=1,column=col+2).value)
        if h3 == "None":
            h3 = " "
        h4 = str(Sheet1.cell(row=1,column=col+3).value)
        if h4 == "None":
            h4 = " "

        d1 = str(Sheet1.cell(row=i,column=col).value)
        if d1 == "None":
            d1 = " "
        d2 = str(Sheet1.cell(row=i,column=col+1).value)
        if d2 == "None":
            d2 = " "
        d3 = str(Sheet1.cell(row=i,column=col+2).value)
        if d3 == "None":
            d3 = " "
        d4 = str(Sheet1.cell(row=i,column=col+3).value)
        if d4 == "None":
            d4 = " "

        tmp = tmp + "\t\t\t\t\t<tr><th>"+h1+"</th><th>"+h2+"</th><th>"+h3+"</th><th>"+h4+"</th></tr>\n"
        tmp = tmp + "\t\t\t\t\t<tr><td>"+d1+"</td><td>"+d2+"</td><td>"+d3+"</td><td>"+d4+"</td></tr>\n"
        col = col+4
        
    tmp = tmp + "\t\t\t\t\t</table>]]>\n" + "\t\t\t\t</description>\n"
    tmp = tmp +  "\t\t\t\t<styleUrl>#msn_blu-blank</styleUrl>\n" 
    tmp = tmp + "\t\t\t\t<Point>\n"
    tmp = tmp + "\t\t\t\t\t<gx:drawOrder>1</gx:drawOrder>\n"
    tmp = tmp + "\t\t\t\t\t<coordinates>"+str(lon)+","+str(lat)+",0</coordinates>\n"
    tmp = tmp + "\t\t\t\t</Point>\n"
    tmp = tmp +"\t\t\t</Placemark>\n"    
    point.writelines(tmp)

tmp = "\t\t</Folder>\n"+"\t</Document>\n"+"</kml>\n"
point.writelines(tmp)
WorkBook.close()
point.close()
t1=time.time()
print("共耗时："+str(t1-t0)+"秒")